﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using QLDHS.DO;
namespace QLDHS.DAL
{
    class HSNS_DAL
    {
        OleDbConnection conn = Program.GetConnection();

        public DataSet loadAllhs()
        {
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                string sql = "select * from HSNS";
                OleDbDataAdapter adp = new OleDbDataAdapter(sql, conn);
                adp.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            conn.Close();
            return ds;

        }

        public DataSet LoalallPhongban()
        {
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                string sql = "select MADV,TENDV from DONVI";
                OleDbDataAdapter adp = new OleDbDataAdapter(sql, conn);
                adp.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            conn.Close();
            return ds;

        }

        public DataSet LoalallChucvu()
        {
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                string sql = "select MACV,TENCV from CHUCVU";
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
                adp.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
           
            return ds;

        }
        public int insertHSHS(HSNS_DO nv)
        {
            int kq = 0;
            try
            {
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    string text = "INSERT INTO HSNS VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    cmd.CommandText = text;
                    try
                    {
                        conn.Open();
                        cmd.Parameters.AddWithValue("@MACB", nv.NvmaCB);
                        cmd.Parameters.AddWithValue("@MACV", nv.NvmaCV);
                        cmd.Parameters.AddWithValue("@HOTEN", nv.Nvhoten);
                        cmd.Parameters.AddWithValue("@NGAYSINH", nv.Nvgaysinh);
                        cmd.Parameters.AddWithValue("@GIOITINH", nv.Nvgioitinh);
                        cmd.Parameters.AddWithValue("@DANTOC", nv.Nvdantoc);
                        cmd.Parameters.AddWithValue("@TONGIAO", nv.Nvtongiao);
                        cmd.Parameters.AddWithValue("@SOCMTND", nv.NvsoCMTND);
                        cmd.Parameters.AddWithValue("@SOBAOHIEM", nv.NvsoBH);
                        cmd.Parameters.AddWithValue("@QUEQUAN", nv.Nvquequan);
                        cmd.Parameters.AddWithValue("@DANGVIEN", nv.NvisDangVien);
                        cmd.Parameters.AddWithValue("@NGAYVAODOAN", nv.Nvngayvaodoan);
                        cmd.Parameters.AddWithValue("@TRINHDO", nv.Nvtrinhdo);
                        cmd.Parameters.AddWithValue("@BIENCHE", nv.Nvbienche);
                        cmd.Parameters.AddWithValue("@HOPDONG", nv.Nvhopdong);
                        cmd.Parameters.AddWithValue("@MADV", nv.NvmaDV);
                        kq = cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException ex)
                    {
                        throw ex;
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return kq;
        }

        public int UpdateNhanvien(HSNS_DO nv)
        {
            int kq = 0;
            try
            {
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    string text = "UPDATE  HSNS SET MACB=?,MACV=?,HOTEN=?,NGAYSINH=?,GIOITINH=?,DANTOC=?,TONGIAO=?,SOCMTND=?,SOBAOHIEM=?,QUEQUAN=?,DANGVIEN=?,NGAYVAODOAN=?,TRINHDO=?,BIENCHE=?,HOPDONG=?,MADV=?";
                    cmd.CommandText = text;
                    try
                    {
                        conn.Open();
                        cmd.Parameters.AddWithValue("@MACB", nv.NvmaCB);
                        cmd.Parameters.AddWithValue("@MACV", nv.NvmaCV);
                        cmd.Parameters.AddWithValue("@HOTEN", nv.Nvhoten);
                        cmd.Parameters.AddWithValue("@NGAYSINH", nv.Nvgaysinh);
                        cmd.Parameters.AddWithValue("@GIOITINH", nv.Nvgioitinh);
                        cmd.Parameters.AddWithValue("@DANTOC", nv.Nvdantoc);
                        cmd.Parameters.AddWithValue("@TONGIAO", nv.Nvtongiao);
                        cmd.Parameters.AddWithValue("@SOCMTND", nv.NvsoCMTND);
                        cmd.Parameters.AddWithValue("@SOBAOHIEM", nv.NvsoBH);
                        cmd.Parameters.AddWithValue("@QUEQUAN", nv.Nvquequan);
                        cmd.Parameters.AddWithValue("@DANGVIEN", nv.NvisDangVien);
                        cmd.Parameters.AddWithValue("@NGAYVAODOAN", nv.Nvngayvaodoan);
                        cmd.Parameters.AddWithValue("@TRINHDO", nv.Nvtrinhdo);
                        cmd.Parameters.AddWithValue("@BIENCHE", nv.Nvbienche);
                        cmd.Parameters.AddWithValue("@HOPDONG", nv.Nvhopdong);
                        cmd.Parameters.AddWithValue("@MADV", nv.NvmaDV);
                        kq = cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException ex)
                    {
                        throw ex;
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return kq;

        }
        public int delHS(string manv)
        {
            int kqdel = 0;
            try
            {
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    string text = "delete FROM  HSNS where MaCB=?";
                    cmd.CommandText = text;
                    try
                    {
                        conn.Open();
                        cmd.Parameters.AddWithValue("@MACB", manv);
                        kqdel = cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException ex)
                    {
                        throw ex;
                    }
                    finally
                    {

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return kqdel;
        }

        public DataTable SearchByname(string name)
        {
            DataTable allcv = new DataTable();

            try
            {
              
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    string text = "SELECT * FROM  HSNS where MaCB LIKE ?";
                    cmd.CommandText = text;
                    try
                    {
                        conn.Open();
                        cmd.Parameters.AddWithValue("@HOVATEN", "%" + name + "%");
                        OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
                        adp.Fill(allcv);
                    }
                    catch (OleDbException ex)
                    {
                        throw ex;
                    }
                    finally
                    {

                    }
                }
                

            }
            finally
            {
                conn.Close();
            }


            return allcv;

        }
    }
}
